/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.garcomeletronico.dao.implementacao;

import br.com.garcomeletronico.bean.Mesa;
import br.com.garcomeletronico.bean.Pedido;
import br.com.garcomeletronico.bean.Prato;
import br.com.garcomeletronico.dao.IDAOPedido;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.GregorianCalendar;
import javax.print.attribute.DateTimeSyntax;

/**
 *
 * @author tulio.luis
 */
public class DAOPedido extends DAOGenerico implements IDAOPedido {

    @Override
    public void fazer(Pedido pojo, Mesa pojo2, List<Prato> listaPratos) throws SQLException {
        try {
            String sql = "insert into Pedido (Mesa_codigo, Funcionario_codigo, hora, valor, status_2, status_3, obs)"
                    + "values (?,?,?,?,?,?,?)";
            PreparedStatement stmt = this.getConexao().prepareStatement(sql);
            stmt.setInt(1, pojo2.getCodigo());
            stmt.setInt(2, 1);
            stmt.setDate(3, new Date(System.currentTimeMillis()));
            stmt.setFloat(4, 0);
            stmt.setString(5, "2 - Aguardando");
            stmt.setString(6, "1 - Em Aberto");
            stmt.setString(7, pojo.getOBS());
            stmt.execute();
            stmt.close();

            for (Prato p : listaPratos) {
                String sql2 = "insert into Pedido_has_prato (Pedido_Codigo, Prato_codigo, Quantidade) "
                        + "values ((select max(codigo) from Pedido), ?, ?)";
                PreparedStatement stmt2 = this.getConexao().prepareStatement(sql2);
                stmt2.setInt(1, p.getCodigo());
                stmt2.setInt(2, p.getQuantidade());
                stmt2.execute();
                stmt2.close();
            }

            String sql3 = "update Pedido "
                    + "set valor ="
                    + " (select SUM(Pedido_has_prato.Quantidade * Prato.preco) from Prato, Pedido_has_Prato where Prato.codigo = Pedido_has_prato.Prato_codigo and Pedido_has_Prato.Pedido_codigo = (select MAX(Pedido_codigo) from Pedido_has_Prato)) where codigo = (select MAX(codigo) from Pedido)";
            PreparedStatement stmt3 = this.getConexao().prepareStatement(sql3);
            stmt3.execute();
            stmt3.close();
        } catch (SQLException e) {
            //throw new SQLException(e.getMessage());
        }
    }

    @Override
    public void atualizarpronto(Pedido pojo) throws SQLException {
        try {
            String sql = ("update Pedido set status_2 = ?"
                    + " where codigo= ?");
            PreparedStatement stmt = this.getConexao().prepareStatement(sql);
            stmt.setString(1, pojo.getStatus_2());
            stmt.setInt(2, pojo.getCodigo());
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        }
    }

    @Override
    public void atualizaraguardando(Pedido pojo) throws SQLException {
        try {
            String sql = ("update Pedido set status_2 = ?"
                    + " where codigo= ?");
            PreparedStatement stmt = this.getConexao().prepareStatement(sql);
            stmt.setString(1, pojo.getStatus_2());
            stmt.setInt(2, pojo.getCodigo());
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        }
    }

    @Override
    public void atualizaratendido(Pedido pojo) throws SQLException {
        try {
            String sql = ("update Pedido set status_2 = ?"
                    + " where codigo= ?");
            PreparedStatement stmt = this.getConexao().prepareStatement(sql);
            stmt.setString(1, pojo.getStatus_2());
            stmt.setInt(2, pojo.getCodigo());
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        }
    }

    @Override
    public void atualizarcancelar(Pedido pojo) throws SQLException {
        try {
            String sql = ("update Pedido set status_2 = ?"
                    + " where codigo= ?");
            PreparedStatement stmt = this.getConexao().prepareStatement(sql);
            stmt.setString(1, pojo.getStatus_2());
            stmt.setInt(2, pojo.getCodigo());
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        }
    }

    @Override
    public List<Pedido> listarTodos() throws SQLException {
        String sql = "select * from Pedido where status_2 <> '3 - Atendido' and status_2 <> '4 - Cancelado'";
        List<Pedido> minhaLista = null;
        PreparedStatement stmt;
        ResultSet rs;

        try {
            stmt = this.getConexao().prepareStatement(sql);
            rs = stmt.executeQuery();

            while (rs.next()) {
                if (minhaLista == null) {
                    minhaLista = new ArrayList<>();
                }

                Pedido p = new Pedido();
                p.setCodigo(rs.getInt("codigo"));
                p.setMesa_Cod(rs.getInt("Mesa_codigo"));
                p.setFunc_Cod(rs.getInt("Funcionario_codigo"));
                p.setHora(rs.getString("hora"));
                p.setOBS(rs.getString("obs"));
                p.setStatus_2(rs.getString("status_2"));
                p.setValor(rs.getFloat("valor"));
                minhaLista.add(p);
            }
            rs.close();
            stmt.close();

        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        }

        return minhaLista;
    }

    @Override
    public void valor(Pedido pojo) throws SQLException {
        try {
            String sql = "select dbo.fx_pedTotal(?)";
            PreparedStatement stmt = this.getConexao().prepareStatement(sql);
            stmt.setInt(2, pojo.getCodigo());
        } catch (SQLException e) {

            throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        }
    }

    @Override
    public List<Pedido> listarPagamentos() throws SQLException {
        String sql = "select * from Pedido where status_2 <> '1 - Pronto' and status_2 <> '2 - Aguardando' and status_2 <> '4 - Cancelado' and status_3 <> '1 - Pago'";
        List<Pedido> minhaLista = null;
        PreparedStatement stmt;
        ResultSet rs;

        try {
            stmt = this.getConexao().prepareStatement(sql);
            rs = stmt.executeQuery();

            while (rs.next()) {
                if (minhaLista == null) {
                    minhaLista = new ArrayList<>();
                }

                Pedido p = new Pedido();
                p.setCodigo(rs.getInt("codigo"));
                p.setStatus_3(rs.getString("status_3"));
                p.setValor(rs.getFloat("valor"));
                p.setMesa_Cod(rs.getInt("Mesa_codigo"));
                minhaLista.add(p);
            }
            rs.close();
            stmt.close();

        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        }

        return minhaLista;
    }

    @Override
    public void Pago(Pedido pojo) throws SQLException {
        try {
            String sql = ("update Pedido set status_3 = ?"
                    + " where codigo= ?");
            PreparedStatement stmt = this.getConexao().prepareStatement(sql);
            stmt.setString(1, pojo.getStatus_3());
            stmt.setInt(2, pojo.getCodigo());
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        }
    }
}